import copy
import datetime
import logging

from flask import Blueprint, make_response, jsonify, request, g
from flask_restful import Resource

import math
import json

from sqlalchemy import text

from apps.dbConnect.dbCurd import dbCurdD
from apps.ext import db
from comm.constant import UID_

from comm.erpApi import apiList
from comm.utils import getOrder
from setting import login_required_api

project = Blueprint('basic', __name__)

class bGListView(Resource):
    @login_required_api
    def get(self):
        # 业务员类型 a.OperatorType = 9
        args = request.args
        search = args.get('search', '')
        sta_dd = args.get('sta_dd', '')
        est_dd = args.get('est_dd', '')
        page = int(args.get('page', 1))
        pmr = {}
        pageSize = 20
        pmr['pageSize'] = pageSize
        pmr['page'] = page
        parmStr = ''
        if search != '':
            pmr['name'] = "%" + search + "%"
            parmStr = " and (a.wr_no like :name or b.name like :name or c.name like :name)"
        if sta_dd is None and sta_dd=='':
            return make_response(jsonify({'msg': '开始日期不能为空'}), 202)
        if est_dd is None and est_dd=='':
            return make_response(jsonify({'msg': '结束日期不能为空'}), 202)
        pmr['sta_dd'] = sta_dd
        pmr['est_dd'] = est_dd
        count_sql = """
select count(*) as TOTAL from mf_wr a 
left join salm b on a.usr_no=b.sal_no
left join dept c on a.dep=c.dep
 where isnull(a.app_name_data,'')='PYTHON-FLASK' and convert(varchar(100), a.wr_dd, 23)>=:sta_dd and convert(varchar(100), a.wr_dd, 23)<=:est_dd  {0}
                """.format(parmStr)
        count_d, err = dbCurdD.dbOneJson(count_sql, pmr)
        if err != None:
            return make_response(jsonify({'msg': '统计查询失败:' + err}), 202)
        if count_d['total'] == 0:
            return make_response(jsonify({'msg': '没有查询到数据'}), 202)
        # 计算总分页
        totalPage = int(math.ceil(count_d['total'] / pageSize))
        if page > int(totalPage):
            return make_response(jsonify({'msg': '没有跟多数据了'}), 202)

        list_sql = """
    SELECT  * 
    FROM (select row_number() over(order by a.wr_dd desc) as rownumber,convert(varchar(100), a.wr_dd, 23) as wr_dd,a.wr_no,a.dep,c.name as dep_name,a.usr_no,b.name as sal_name,a.usr from mf_wr a 
left join salm b on a.usr_no=b.sal_no
left join dept c on a.dep=c.dep
 where isnull(a.app_name_data,'')='PYTHON-FLASK' and convert(varchar(100), a.wr_dd, 23)>=:sta_dd and convert(varchar(100), a.wr_dd, 23)<=:est_dd {0}) TEMP_ROW
    WHERE ROWNUMBER>((:page - 1) * :pageSize) and ROWNUMBER<=((:page) * :pageSize)
                """.format(parmStr)
        list_d, err = dbCurdD.dbListJon(list_sql, pmr)
        if err != None:
            # print(err)
            return make_response(jsonify({'msg': '明细查询失败:' + err}), 202)
        # dataList = [
        #     {"ti_no":"TI20230609001","cus_no":"a0001","cus_name":"广州天心软件科技有限公司","ti_dd":"2023-06-09","select":False},
        #     {"ti_no":"TI20230609002","cus_no":"a0002","cus_name":"深圳天心软件科技有限公司aaaadasdsadasdarewasfsfsdf","ti_dd":"2023-06-10","select":False}
        # ]
        previousPage = False if page <= 1 else True
        nextPage = False if page >= totalPage else True
        result = {}
        result['data'] = list_d
        # 分页大小
        result['pageSize'] = pageSize
        # 是否有上一页
        result['previousPage'] = previousPage
        # 是否有下一页
        result['nextPage'] = nextPage
        # 总分页
        result['totalPage'] = totalPage
        # 当前页
        result['page'] = page
        # 总记录
        result['total'] = count_d['total']
        msg = {'msg': '成功', "result": result}
        return make_response(jsonify(msg), 200)
    @login_required_api
    def post(self):
        args = request.get_json()
        # 作业人员
        sal_no = args.get('sal_no', None)
        use = args.get('use', None)
        bb_no = args.get('bb_no', None)
        dep = args.get('dep', None)
        # 表头备注
        rem = args.get('rem', None)
        # 数量
        qty = args.get('qty', 0)
        # 工时
        ut_time = args.get('ut_time', 0)
        # 制程
        zc_no = args.get('zc_no', None)
        # 通知单号
        tz_no = args.get('tz_no', None)
        # 经办人员
        sal_no_z = args.get('sal_no_z', None)
        # 成品编码
        mrp_no = args.get('mrp_no', None)
        # 成品名称
        mrp_name = args.get('mrp_name', None)
        # 报工日期
        dd = args.get('dd', None)
        # 开始日期
        sta_dd = args.get('sta_dd', None)
        # 结束日期
        est_dd = args.get('est_dd', None)
        # 制令单号
        mo_no = args.get('mo_no', None)
        jitai = args.get('jitai', None)
        id_no = args.get('id_no', None)
        qty_count = args.get('qty_count', None)
        if sal_no is None:
            sal_no=[]
        if sal_no =='':
            sal_no=[]
        if type(sal_no)!=list:
            return make_response(jsonify({"msg": "作业人员参数错误"}), 202)

        qty = float(qty)
        ut_time = float(ut_time)
        qty_ = copy.deepcopy(float(qty))
        if qty <=0:
            return make_response(jsonify({"msg": "报工数量不能小于或等于0"}), 202)

        # if ut_time <=0:
        #     return make_response(jsonify({"msg": "报工工时不能小于或等于0"}), 202)

        if zc_no is None and zc_no=='':
            return make_response(jsonify({"msg": "制程不能为空"}), 202)

        # if sal_no_z is None and sal_no_z=='':
        #     return make_response(jsonify({"msg": "经办人员不能为空"}), 202)

        if mrp_no is None and mrp_no=='':
            return make_response(jsonify({"msg": "成品编码不能为空"}), 202)

        if dd is None and dd=='':
            return make_response(jsonify({"msg": "报工日期不能为空"}), 202)


        if sta_dd is None and sta_dd=='':
            return make_response(jsonify({"msg": "开始日期不能为空"}), 202)

        if est_dd is None and est_dd=='':
            return make_response(jsonify({"msg": "结束日期不能为空"}), 202)

        if use is None and use == '':
            return make_response(jsonify({"msg": "制单人不能为空"}), 202)

        if dep is None and dep=='':
            return make_response(jsonify({"msg": "用户归属部门不能为空"}), 202)

        if mo_no is None and mo_no=='':
            return make_response(jsonify({"msg": "制令单号不能为空"}), 202)

        if tz_no is None and tz_no=='':
            return make_response(jsonify({"msg": "通知单号不能为空"}), 202)

        # 处理作业人员
        sal_no_list = []
        if len(sal_no)>0:
            sal_no_list = list( map(lambda x:x["value"],sal_no))
        # 查询最小报工套数
        sql = "select cast(min(isnull(qty_rtn,0)) as float) as qty_rtn from tf_tz where tz_no=:tz_no"
        data_z, err = dbCurdD.dbOneJson(sql, {"tz_no":tz_no})

        if err != None:
            return make_response(jsonify({"msg": tz_no + err}), 202)
        if qty > data_z['qty_rtn']:
            print(data_z['qty_rtn'])
            return make_response(jsonify({"msg": tz_no + '超出最大领料报工量%d' % data_z['qty_rtn']} ), 202)

#         sql1 = """
# select  a007.tz_no as tz_no_z,a006.zc_no,a006.name as zc_name,a001.tz_no,a001.mo_no,a001.id_no,a001.qty as qty_count,isnull(a001.qty_fin,0) as qty_fin,isnull(a002.qty_fin,0) as qty_fin_s,case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as d_qty,
# case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as qty,
#  a004.name as mrp_name,a001.mrp_no,a001.dep as jitai,a005.name as jitai_name,a001.rem from
# /*查询当前制程*/
# mf_tz a001 with (nolock)
# /*查询上制成数据*/
# left join mf_tz a002  with (nolock) on a001.mo_no=a002.mo_no and isnull(a001.zc_no_up,'')=a002.zc_no
# left join (select sum(isnull(qty_rtn,0)) as qty_rtn,tz_no from tf_tz group by tz_no) a003  on a001.tz_no=a003.tz_no
# left join prdt a004 with (nolock) on a001.mrp_no=a004.prd_no
# left join dept a005 with (nolock)  on a001.dep=a005.dep
# left join zc_no a006 with (nolock) on a001.zc_no=a006.zc_no
# left join mf_tz a007 with (nolock) on a001.mo_no=a007.mo_no and a001.zc_no_dn = a007.zc_no
# where case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end >0 and
# isnull(a001.close_id,'')!='T' and case when isnull(a003.tz_no,'')='' then 999 else isnull(a003.qty_rtn,0) end > 0
#  and a001.tz_no=:tz_no
#         """

        sql1 = """
select  a007.tz_no as tz_no_z,a006.zc_no,a006.name as zc_name,a001.tz_no,a001.mo_no,a001.id_no,a001.qty as qty_count,isnull(a001.qty_fin,0) as qty_fin,isnull(a002.qty_fin,0) as qty_fin_s,case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as d_qty,
case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as qty,
 a004.name as mrp_name,a001.mrp_no,a001.dep as jitai,a005.name as jitai_name,a001.rem from 
/*查询当前制程*/
mf_tz a001 with (nolock)
/*查询上制成数据*/
left join (
SELECT MO_NO,ZC_NO_UP,ZC_NO,QTY,QTY_FIN FROM MF_TZ WITH (NOLOCK) 
WHERE ISNULL(MO_NO,'')<>'' 
UNION 
SELECT MO_NO,ZC_NO_UP,ZC_NO,QTY,QTY_RTN FROM MF_TW WITH (NOLOCK) 
WHERE ISNULL(MO_NO,'')<>'' 
) a002 on a001.mo_no=a002.mo_no and isnull(a001.zc_no_up,'')=a002.zc_no
left join prdt a004 with (nolock) on a001.mrp_no=a004.prd_no
left join dept a005 with (nolock)  on a001.dep=a005.dep
left join zc_no a006 with (nolock) on a001.zc_no=a006.zc_no
left join mf_tz a007 with (nolock) on a001.mo_no=a007.mo_no and a001.zc_no_dn = a007.zc_no
where case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end >0
 and a001.tz_no=:tz_no
        """
        # 查询可以报工量
        sum_data,err = dbCurdD.dbOneJson(sql1,{"tz_no":tz_no})
        if err !=None:
            return make_response(jsonify({"msg": "查询可报工量失败" + err}), 202)
        if sum_data == None:
            return make_response(jsonify({"msg": "此单据已报工完成"}), 202)
        if sum_data['d_qty'] ==0 :
            return make_response(jsonify({"msg": "可报工量为0"}), 202)
        # 现在的时间
        now = datetime.datetime.now()

        # 时间格式化
        formatted_time = now.strftime("%Y-%m-%d %H:%M:%S")
        # 1、获取单号
        order, err = getOrder(db=db.session, tableName="MF_WR", filed='WR_NO', prefix="WRZ", num=3)
        if err != None:
            return make_response(jsonify({"msg": '生产报工单失败' + err}), 202)
        # 报工单表头
        mf_wr = {
            "wr_no": order,
            "wr_dd": dd,
            "usr_no": sal_no_z,
            "grp_id": bb_no,
            "rem": rem,
            "dep": dep,
            "usr": use,
            "chk_man": use,
            "cls_date": formatted_time,
            "sys_date": formatted_time,
            "app_name_data":'PYTHON-FLASK'
        }

        # 1、插入表头
        mf_wr_keys = mf_wr.keys()
        mf_wr_keysStr = ','.join(mf_wr_keys)
        mf_wr_valuesStr = ':' + ',:'.join(mf_wr_keys)
        mf_wr_sql = """
        insert into mf_wr({key})
        values({value})
        """.format(key=mf_wr_keysStr, value=mf_wr_valuesStr)
        try:
            db.session.execute(text(mf_wr_sql), mf_wr)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '新增日报表表头语句执行失败' + repr(e) + '语句' + mf_wr_sql + '参数' + str(mf_wr)}), 202)

        # 报工单表身
        tf_wr = {}
        tf_wr["itm"] = 1
        tf_wr["pre_itm"] = 1
        tf_wr["wr_no"] = order
        tf_wr["tz_no"] = tz_no
        tf_wr["mo_no"] = mo_no
        tf_wr["usr_no"] = sal_no_z
        tf_wr["sta_time"] = sta_dd
        tf_wr["end_time"] = est_dd
        tf_wr["zc_no"] = zc_no
        tf_wr["tz_no2"] = sum_data['tz_no_z']
        tf_wr["dep"] = jitai
        tf_wr["unit"] = 1
        tf_wr["id_no"] = id_no
        tf_wr["qty"] = qty_count
        tf_wr["prd_no"] = mrp_no
        tf_wr["tr_no"] = ""
        tf_wr["qty_fin"] = qty
        if ut_time<=0:
            ut_time = 0
        tf_wr["time_used"] = UID_.new_round(ut_time, 0)

        # 2、插入表身
        tf_wr_keys = tf_wr.keys()
        tf_wr_keysStr = ','.join(tf_wr_keys)
        tf_wr_valuesStr = ':' + ',:'.join(tf_wr_keys)
        tf_wr_sql = """
        insert into tf_wr({key})
        values({value})
        """.format(key=tf_wr_keysStr, value=tf_wr_valuesStr)
        try:
            db.session.execute(text(tf_wr_sql), tf_wr)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '新增日报表表身语句执行失败' + repr(e) + '语句' + tf_wr_sql + '参数' + str(tf_wr)}),202)

        # 组表头
        mf_mk_yg = {
            "bil_id": "WR",
            "bil_no": order,
            "usr": use,
            "sys_date": formatted_time,
            "bil_dd": dd
        }

        # 3、插入组表头
        mf_mk_yg_keys = mf_mk_yg.keys()
        mf_mk_yg_keysStr = ','.join(mf_mk_yg_keys)
        mf_mk_yg_valuesStr = ':' + ',:'.join(mf_mk_yg_keys)
        mf_mk_yg_sql = """
        insert into mf_mk_yg({key})
        values({value})
        """.format(key=mf_mk_yg_keysStr, value=mf_mk_yg_valuesStr)
        try:
            db.session.execute(text(mf_mk_yg_sql), mf_mk_yg)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '新增日报表组表头语句执行失败' + repr(e) + '语句' + mf_mk_yg_sql + '参数' + str(mf_mk_yg)}), 202)

        # 组表身

        sal_no_list_qty = UID_.new_round(tf_wr["qty_fin"] / len(sal_no_list), 0)
        ut_time_z = UID_.new_round(float(tf_wr["time_used"]) / len(sal_no_list), 0)

        # 处理作业人员
        for index,itm1 in enumerate(sal_no_list):
            mk_yg = {
                "bil_id": "WR",
                "itm": index + 1,
                "mod_no": "",
                "seb_no": "",
                "unit": 1,
                "yg_no": itm1,
                "qty": sal_no_list_qty,
                "use_time": ut_time_z,
                "bil_no": order,
                "spc_no": "",
                "zc_no": zc_no,
                "prd_no": mrp_no,
                "bom_no": id_no,
                "bil_dd": dd,
                "usr": use,
            }

            # 4、插入组表身
            mk_yg_keys = mk_yg.keys()
            mk_yg_keysStr = ','.join(mk_yg_keys)
            mk_yg_valuesStr = ':' + ',:'.join(mk_yg_keys)
            mk_yg_sql = """
            insert into mk_yg({key})
            values({value})
            """.format(key=mk_yg_keysStr, value=mk_yg_valuesStr)
            try:
                db.session.execute(text(mk_yg_sql), mk_yg)
                # session.commit()
            except Exception as e:
                db.session.rollback()
                db.session.close()
                logging.info('新增日报表组表身语句执行失败' + repr(e) + '语句' + mk_yg_sql + '参数' + str(mk_yg))
                return make_response(jsonify({"msg":'新增日报表组表身语句执行失败' + repr(e) + '语句' + mk_yg_sql + '参数' + str(mk_yg)}), 202)

        # mf_tz_sql = """
        # update mf_tz set qty_fin = isnull(qty_fin,0) + :qty,qty_mv = isnull(qty_mv,0) + :qty,qty_prc = isnull(qty_prc,0) - isnull(qty_fin,0) - :qty where tz_no=:tz_no
        # """
        mf_tz_sql = """
update a set a.qty_fin = isnull(a.qty_fin,0) + :qty,a.qty_mv = isnull(a.qty_mv,0) + :qty,a.qty_prc = case when isnull(b.tz_no,'')='' then isnull(a.qty_prc,0)-isnull(a.qty_fin,0) - :qty  else isnull(b.qty_fin,0)-isnull(a.qty_fin,0) - :qty  end 
from mf_tz a 
left join mf_tz b on a.mo_no=b.mo_no and a.zc_no_up=b.zc_no
where a.tz_no=:tz_no
        """
        try:
            db.session.execute(text(mf_tz_sql), {'qty':qty,'tz_no':tz_no})
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg":'更新通知单产出量语句执行失败' + repr(e) + '语句' + mf_tz_sql + '参数' + str({'qty':qty,'tz_no':tz_no})}), 202)
        # 更新下到工序的qty_prc
        mf_tz_z_sql = """
        update b set b.qty_prc=isnull(a.qty_fin,0) - isnull(b.qty_fin,0) from mf_tz a
        inner join mf_tz b on a.mo_no=b.mo_no and a.zc_no_dn = b.zc_no
        where a.tz_no=:tz_no
        """
        try:
            db.session.execute(text(mf_tz_z_sql), {'qty': qty, 'tz_no': tz_no})
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '更新通知单下制成可转量语句执行失败' + repr(
                e) + '语句' + mf_tz_z_sql + '参数' + str({'tz_no': tz_no})}), 202)
        db.session.commit()
        return make_response(jsonify({"msg":'成功'}), 200)

class bGListPkView(Resource):
    @login_required_api
    def get(self,pk):
        sql1 = """
select row_number() over(order by a.wr_dd desc) as rownumber,convert(varchar(100), a.wr_dd, 23) as wr_dd,a.wr_no,a.dep,c.name as dep_name,a.usr_no,b.name as sal_name,a.usr from mf_wr a 
left join salm b on a.usr_no=b.sal_no
left join dept c on a.dep=c.dep
where isnull(a.app_name_data,'')='PYTHON-FLASK' and a.wr_no=:pk
        """
        sql2 = """
 select a.wr_no+'-'+cast(a.itm as varchar(20)) as id,isnull(a.qty_fin,0) as qty_fin, a.prd_no,isnull(a.qty,0) as qty,a.tz_no,a.mo_no,isnull(a.time_used,0) as time_used,b.name as prd_name,c.name as zc_name from tf_wr a
 left join prdt b on a.prd_no=b.prd_no
 left join zc_no c on a.zc_no=c.zc_no
 where a.wr_no=:pk
        """
        parm = {
            "pk":pk
        }
        mf_data,err = dbCurdD.dbOneJson(sql1,parm=parm)
        if err != None:
            return make_response(jsonify({'msg': '表头查询失败:' + err}), 202)
        if mf_data is None:
            return make_response(jsonify({'msg': '没有查询到数据'}), 202)
        tf_data,err = dbCurdD.dbListJon(sql2,parm=parm)
        if err != None:
            return make_response(jsonify({'msg': '表身查询失败:' + err}), 202)
        if len(tf_data)==0:
            return make_response(jsonify({'msg': '没有查询到表身数据'}), 202)
        result = {
            "mf":mf_data,
            "tf":tf_data
        }
        msg = {'msg': '成功', "result": result}
        return make_response(jsonify(msg), 200)

    @login_required_api
    def delete(self,pk):

        sql1 = """
select a.tz_no,a.qty_fin,
case when isnull(c.tz_no,'')='' then 'T' when isnull(b.qty_fin,0)-isnull(a.qty_fin,0)>=isnull(c.qty_fin,0) then 'T' else 'F' end isdelete 
from tf_wr a with (nolock)
left join mf_tz b with (nolock) on a.tz_no= b.tz_no
left join mf_tz c with (nolock) on b.zc_no_dn = c.zc_no and b.mo_no=c.mo_no
where a.wr_no=:pk
        """
        sql2 = "delete from mk_yg where bil_no=:pk"
        sql3 = "delete from mf_mk_yg where bil_no=:pk"
        sql4 = "delete from tf_wr where wr_no=:pk"
        sql5 = "delete from mf_wr where wr_no=:pk"
        sql6 = "update mf_tz set qty_fin = isnull(qty_fin,0) - :qty,qty_mv = isnull(qty_mv,0) - :qty,qty_prc = isnull(qty_prc,0) + :qty where tz_no=:tz_no"
        parm = {}
        parm['pk'] = pk
        # 查询报工单
        wr_data, err = dbCurdD.dbOneJson(sql1, parm)
        if err != None:
            return make_response(jsonify({"msg": '报工单查询失败1' + err}), 202)
        if wr_data == None:
            return make_response(jsonify({"msg": '报工单查询失败2'}), 202)
        parm['tz_no'] = wr_data['tz_no']
        parm['qty'] = wr_data['qty_fin']
        if wr_data['isdelete']!='T':
            return make_response(jsonify({"msg": '完成量大于下制成完成量禁止删除'}), 202)
        # 删除组表身
        try:
            db.session.execute(text(sql2), parm)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '删除失败1' + repr(e)}), 202)
        # 删除组表头
        try:
            db.session.execute(text(sql3), parm)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '删除失败2' + repr(e)}), 202)
        # 删除报工单表身
        try:
            db.session.execute(text(sql4), parm)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '删除失败3' + repr(e)}), 202)
        # 删除报告单表头
        try:
            db.session.execute(text(sql5), parm)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '删除失败4' + repr(e)}), 202)
        # 更新通知单
        try:
            db.session.execute(text(sql6), parm)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '删除失败6' + repr(e)}), 202)

        # 更新下到工序的qty_prc
        mf_tz_z_sql = """
        update b set b.qty_prc=isnull(b.qty_prc,0) - :qty from mf_tz a
        inner join mf_tz b on a.mo_no=b.mo_no and a.zc_no_dn = b.zc_no
        where a.tz_no=:tz_no
        """
        try:
            db.session.execute(text(mf_tz_z_sql), parm)
            # session.commit()
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return make_response(jsonify({"msg": '更新通知单下制成可转量语句执行失败' + repr(
                e) + '语句' + mf_tz_z_sql + '参数' + str(parm)}), 202)
        db.session.commit()
        return make_response(jsonify({"msg": '成功'}), 200)

class bGgroupView(Resource):
    @login_required_api
    def get(self):
        args = request.args
        search = args.get('search', '')
        page = int(args.get('page', 1))
        pmr = {}
        pageSize = 50
        pmr['pageSize'] = pageSize
        pmr['page'] = page
        parmStr = ''
        if search != '':
            pmr['name'] = "%" + search + "%"
            parmStr = " and GROUP_NO like :name"
        count_sql = """
select count(*) as TOTAL from PDA_EMP_GROUP where GROUP_NO!='' {0} group by GROUP_NO
            """.format(parmStr)
        count_d, err = dbCurdD.dbOneJson(count_sql, pmr)
        if err != None:
            return make_response(jsonify({'msg': '统计查询失败:' + err}), 202)
        if count_d['total'] == 0:
            return make_response(jsonify({'msg': '没有查询到数据'}), 202)
        # 计算总分页
        totalPage = int(math.ceil(count_d['total'] / pageSize))
        if page > int(totalPage):
            return make_response(jsonify({'msg': '没有跟多数据了'}), 202)

        list_sql = """
SELECT  * 
FROM (select ROW_NUMBER() OVER(ORDER BY GROUP_NO DESC) AS ROWNUMBER, GROUP_NO AS VALUE,GROUP_NO AS LABEL,0 AS [SELECT] from PDA_EMP_GROUP where GROUP_NO!='' {0} group by GROUP_NO) TEMP_ROW
WHERE ROWNUMBER>((:page - 1) * :pageSize) and ROWNUMBER<=((:page) * :pageSize)
            """.format(parmStr)
        list_d, err = dbCurdD.dbListJon(list_sql, pmr)
        if err != None:
            return make_response(jsonify({'msg': '明细查询失败:' + err}), 202)
        # dataList = [
        #     {"ti_no":"TI20230609001","cus_no":"a0001","cus_name":"广州天心软件科技有限公司","ti_dd":"2023-06-09","select":False},
        #     {"ti_no":"TI20230609002","cus_no":"a0002","cus_name":"深圳天心软件科技有限公司aaaadasdsadasdarewasfsfsdf","ti_dd":"2023-06-10","select":False}
        # ]
        previousPage = False if page <= 1 else True
        nextPage = False if page >= totalPage else True
        result = {}
        result['data'] = list_d
        # 分页大小
        result['pageSize'] = pageSize
        # 是否有上一页
        result['previousPage'] = previousPage
        # 是否有下一页
        result['nextPage'] = nextPage
        # 总分页
        result['totalPage'] = totalPage
        # 当前页
        result['page'] = page
        # 总记录
        result['total'] = count_d['total']
        msg = {'msg': '成功', "result": result}
        return make_response(jsonify(msg), 200)

class salmListView(Resource):
    @login_required_api
    def get(self):
        # 业务员类型 a.OperatorType = 9
        args = request.args
        search = args.get('search', '')
        page = int(args.get('page', 1))
        # operatortype = args.get('operatortype', None)
        pmr = {}
        pageSize = 50
        pmr['pageSize'] = pageSize
        pmr['page'] = page
        parmStr = ''
        if search != '':
            pmr['name'] = "%" + search + "%"
            parmStr = " and (name like :name or yg_no like :name)"
        count_sql = """
select count(*) as TOTAL from MF_YG where yg_no!='' {0}
            """.format(parmStr)
        count_d, err = dbCurdD.dbOneJson(count_sql, pmr)
        if err != None:
            return make_response(jsonify({'msg': '统计查询失败:' + err}), 202)
        if count_d['total'] == 0:
            return make_response(jsonify({'msg': '没有查询到数据'}), 202)
        # 计算总分页
        totalPage = int(math.ceil(count_d['total'] / pageSize))
        if page > int(totalPage):
            return make_response(jsonify({'msg': '没有跟多数据了'}), 202)

        list_sql = """
SELECT  * 
FROM (select ROW_NUMBER() OVER(ORDER BY yg_no DESC) AS ROWNUMBER, yg_no AS VALUE,yg_no+';'+name AS LABEL,0 AS [SELECT] from MF_YG where yg_no!='' {0}) TEMP_ROW
WHERE ROWNUMBER>((:page - 1) * :pageSize) and ROWNUMBER<=((:page) * :pageSize)
            """.format(parmStr)
        list_d, err = dbCurdD.dbListJon(list_sql, pmr)
        if err != None:
            return make_response(jsonify({'msg': '明细查询失败:' + err}), 202)
        # dataList = [
        #     {"ti_no":"TI20230609001","cus_no":"a0001","cus_name":"广州天心软件科技有限公司","ti_dd":"2023-06-09","select":False},
        #     {"ti_no":"TI20230609002","cus_no":"a0002","cus_name":"深圳天心软件科技有限公司aaaadasdsadasdarewasfsfsdf","ti_dd":"2023-06-10","select":False}
        # ]
        previousPage = False if page <= 1 else True
        nextPage = False if page >= totalPage else True
        result = {}
        result['data'] = list_d
        # 分页大小
        result['pageSize'] = pageSize
        # 是否有上一页
        result['previousPage'] = previousPage
        # 是否有下一页
        result['nextPage'] = nextPage
        # 总分页
        result['totalPage'] = totalPage
        # 当前页
        result['page'] = page
        # 总记录
        result['total'] = count_d['total']
        msg = {'msg': '成功', "result": result}
        return make_response(jsonify(msg), 200)

class scanListView(Resource):
    @login_required_api
    def post(self):
        args = request.get_json()
        scan = args.get('scan',None)
        msg = {}
        if scan is None and scan=='':
            msg['msg'] = '条码不能为空'

        parameter = {}
        stripped_scan = scan.strip()
        parameter['tz_no'] = stripped_scan[0:-1]
        # 判断通知单是否存在
        sql = "select tz_no from mf_tz where tz_no=:tz_no"
        data_z, err = dbCurdD.dbOneJson(sql, parameter)
        if err != None:
            msg['msg'] = parameter['zc_no'] + err
            return make_response(jsonify(msg), 202)
        if data_z == None:
            msg['msg'] = '通知单不存在'
            return make_response(jsonify(msg), 202)

        # 判断是否领料
        sql = "select sum(isnull(qty_rtn,0)) as qty_rtn from tf_tz where tz_no=:tz_no"
        data_z, err = dbCurdD.dbOneJson(sql, parameter)
        if err != None:
            msg['msg'] = parameter['zc_no'] + err
            return make_response(jsonify(msg), 202)
        if data_z['qty_rtn'] == 0:
            msg['msg'] = parameter['tz_no']+'未领料禁止报工'
            return make_response(jsonify(msg), 202)

    #     sql1 = """
    # select a006.zc_no,a006.name as zc_name,a001.tz_no,a001.mo_no,a001.id_no,a001.qty as qty_count,isnull(a001.qty_fin,0) as qty_fin,isnull(a002.qty_fin,0) as qty_fin_s,case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as d_qty,
    # case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as qty,
    #  a004.name as mrp_name,a001.mrp_no,a001.dep as jitai,a005.name as jitai_name,a001.rem from
    # /*查询当前制程*/
    # mf_tz a001 with (nolock)
    # /*查询上制成数据*/
    # left join mf_tz a002  with (nolock) on a001.mo_no=a002.mo_no and isnull(a001.zc_no_up,'')=a002.zc_no
    # left join prdt a004 with (nolock) on a001.mrp_no=a004.prd_no
    # left join dept a005 with (nolock)  on a001.dep=a005.dep
    # left join zc_no a006 with (nolock) on a001.zc_no=a006.zc_no
    # where case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end >0
    #  and a001.tz_no=:tz_no
    #         """
        sql1 = """
select  a007.tz_no as tz_no_z,a006.zc_no,a006.name as zc_name,a001.tz_no,a001.mo_no,a001.id_no,a001.qty as qty_count,isnull(a001.qty_fin,0) as qty_fin,isnull(a002.qty_fin,0) as qty_fin_s,case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as d_qty,
case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end as qty,
 a004.name as mrp_name,a001.mrp_no,a001.dep as jitai,a005.name as jitai_name,a001.rem from 
/*查询当前制程*/
mf_tz a001 with (nolock)
/*查询上制成数据*/
left join (
SELECT MO_NO,ZC_NO_UP,ZC_NO,QTY,QTY_FIN FROM MF_TZ WITH (NOLOCK) 
WHERE ISNULL(MO_NO,'')<>'' 
UNION 
SELECT MO_NO,ZC_NO_UP,ZC_NO,QTY,QTY_RTN FROM MF_TW WITH (NOLOCK) 
WHERE ISNULL(MO_NO,'')<>'' 
) a002 on a001.mo_no=a002.mo_no and isnull(a001.zc_no_up,'')=a002.zc_no
left join prdt a004 with (nolock) on a001.mrp_no=a004.prd_no
left join dept a005 with (nolock)  on a001.dep=a005.dep
left join zc_no a006 with (nolock) on a001.zc_no=a006.zc_no
left join mf_tz a007 with (nolock) on a001.mo_no=a007.mo_no and a001.zc_no_dn = a007.zc_no
where case when isnull(a001.zc_no_up,'')='' then a001.qty - isnull(a001.qty_fin,0) else isnull(a002.qty_fin,0) - isnull(a001.qty_fin,0) end >0
 and a001.tz_no=:tz_no  
        """
        data_z, err = dbCurdD.dbOneJson(sql1, parameter)
        if err != None:
            msg['msg'] =  parameter['tz_no'] + '没有需要报工的数据' + err
            return make_response(jsonify(msg), 202)
        if data_z == None:
            msg['msg'] = parameter['tz_no'] + '没有需要报工的数据'
            return make_response(jsonify(msg), 202)
        msg['msg'] = '成功'
        msg['result'] = data_z
        return make_response(jsonify(msg), 200)